{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Intro","Stop Assignment","Route Assignment","Final Checklist"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Intro","index":0,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":false,"merged":[[7,1,10,10]]}
[]
[]
[]
[]
[]
[]
[]
[null,"This file is designed to help participants better understand the evaluation process by showing you the way that we will check both the compliance and efficacy of your submission. While the goal of your submission remains to minimize the number of required buses, miles, and driving hours (the table outlined in the Transportation Challenge Overview document on our website) - we also want to make sure that the playing field is level and that every submission is compliant. That is, for example, we want to ensure that folks aren't using overly optimistic drive times between points. Our goal with this file is to support you in being compliant and supporting you in triple-checking your solution. \n\nTo do this, the file has two tabs. The first addresses stops: \n       - Is every student assigned to a stop?\n       - Is every stop an appropriate distance away?\n       - Where did you use existing stops and where did you use new stops (if you choose to make new stops)?\n\nThe second tab addresses routes: \n       - Does every student have a route that will take them to and from the right school?\n       - Are the stops honestly placed - that is, do the distances and times between stops mirror reality?\n       - Are any buses over-subscribed at any point in the day?\n\nWhen you are done - you certainly are not required to submit this specific file. However, your submission is required to include all of the information on the stop assignment and route assignment tab. \n\nAs always, please reach out with any questions to weger@bostonpublicschools.org"]

{"sheet":"Stop Assignment","index":1,"n_rows":5,"n_cols":11,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,"Legend -->","Cells in blue should be copy and pasted from the dataset BPS shared with you","Cells in orange you should fill in"]
[]
[null,null,"Student ID","Student Address Latitude","Student Address Longitude","Stop ID","Stop Latitude","Stop Longitude","Your Estimated Walk to Stop Distance","Student Max Walk to Stop Distance","Assigned Bus ID"]
[null,"Notes","Please triple-check to ensure that you include all students",null,null,"If a student is a D2D student, use that student's address. For corner students, include the Stop ID. If you created this stop (i.e. it doesn't have a formal stop ID) use the format X1, X2…",null,null,"Leave blank for D2D students and please round to nearest 100th for corner students","This is the value from the database we shared with you","Which bus is this student riding?"]

{"sheet":"Route Assignment","index":2,"n_rows":21,"n_cols":21,"rows_kept":21,"cut":false,"hidden":false,"merged":[[1,1,1,10],[17,1,19,5],[17,8,17,10],[1,12,1,20]]}
[]
[null,"Example ",null,null,null,null,null,null,null,null,null,null,"To be completed by participants"]
[null,"Bus ID","Stop ID","Stop Latitude","Stop Longitude","Cumulative Number of Students on Bus","Estimated Drive Time Between Stops (minutes)","Estimated Mileage Between Stops (Miles)","Stop Wait Time","Estimated Arrival Time","Notes",null,"Bus ID","Stop ID","Stop Latitude","Stop Longitude","Cumulative Number of Students on Bus","Estimated Drive Time Between Stops (minutes)","Estimated Mileage Between Stops (Miles)","Stop Wait Time","Estimated Arrival Time"]
[null,"HS114","Washington Street Bus Yard",42.333525000000002,-71.080438999999998,0,"NA","NA","00:00:00",{"$f":"J5-G5-I4","v":"06:49:23"},"All buses must begin at bus yard"]
[null,"HS114","82 Lenox Street",42.336934999999997,-71.081586000000001,1,{"$f":"GetDuration(D4&\",\"&E4,D5&\",\"&E5)/86400","v":"00:02:18"},{"$f":"GetDistance(D4&\",\"&E4,D5&\",\"&E5)*0.000621371","v":0.32808388799999999},{"$f":"$I$19+(F5-F4)*$I$20","v":"00:00:35"},{"$f":"J6-G6-I5","v":"06:51:41"},"This first leg - from yard to first stop - is called \"dead-head\"; basically drive time without any students on it"]
[null,"HS114","X1",42.322892000000003,-71.076706999999999,6,{"$f":"GetDuration(D5&\",\"&E5,D6&\",\"&E6)/86400","v":"00:08:29"},{"$f":"GetDistance(D5&\",\"&E5,D6&\",\"&E6)*0.000621371","v":1.439095236},{"$f":"$I$19+(F6-F5)*$I$20","v":"00:00:55"},{"$f":"J7-G7-I6","v":"07:00:45"},"This stop is X1 - the first stop created by a participant, not BPS"]
[null,"HS114",1390,42.327736000000002,-71.077481000000006,11,"00:02:57",0.40575526300000003,"00:00:55",{"$f":"J8-G8-I7","v":"07:04:37"},"This stop, 1390, is an existing BPS stop"]
[null,"HS114",1540,42.326030000000003,-71.072695999999993,13,"00:01:37",0.32000606500000001,"00:00:40",{"$f":"J9-G9-I8","v":"07:07:09"}]
[null,"HS114","Ellis School",42.316896999999997,-71.092282999999995,0,"00:07:11",1.375715394,{"$f":"$I$21","v":"00:10:00"},"07:15:00","Schools have a negative number here to demark students exiting the bus"]
[null,"HS114","X2",42.333599999999997,-71.077000999999996,2,"00:08:11",1.742945655,"00:00:40",{"$f":"J11-G11-I10","v":"07:46:58"},"Because the ensuing route does not require a full hour of drive time, the bus can start it's next route much later"]
[null,"HS114",1392,42.335658000000002,-71.082852000000003,7,"00:03:12",0.46478550800000001,"00:00:55",{"$f":"J12-G12-I11","v":"07:50:50"}]
[null,"HS114","X5",42.318249000000002,-71.086146999999997,15,"00:07:51",1.557155726,"00:01:10",{"$f":"J13-G13-I12","v":"07:59:36"}]
[null,"HS114","107 Harrishof St",42.316820999999997,-71.088571999999999,16,"00:01:54",0.37841493900000001,"00:00:35","08:02:40","This stop is a D2D stop since it is an address with one student getting on the bus"]
[null,"HS114","55 Crawford Street",42.312097000000001,-71.086644000000007,17,"00:02:14",0.50268913900000001,"00:00:35","08:05:29"]
[null,"HS114","Mason School",42.326155,-71.071278000000007,0,"00:08:56",1.660924683,{"$f":"$I$21","v":"00:10:00"},"08:15:00"]
[null,"HS114","Washington Street Bus Yard",42.333525000000002,-71.080438999999998,0,"00:04:02",0.82393794600000003,"00:00:00",{"$f":"J15+G16+I15","v":"08:29:02"},"All buses must end at bus yard"]
[null,"*Please ensure that your stops are sequential (the estimated arrival time column should help with this)"]
[null,"*All stops are based on fictional locations and may, or may not, be actual BPS bus stops. Drive times based on embedded VBA code quering the Google Maps API",null,null,null,null,null,null,"Key Time Intervals"]
[null,null,null,null,null,null,null,null,"00:00:30","<-- All stops are at least 30 seconds"]
[null,null,null,null,null,null,null,null,"00:00:05","<-- Plus 5 seconds per student at stop"]
[null,null,null,null,null,null,null,null,"00:10:00","<-- Buses need 10 minutes to drop off all students"]

{"sheet":"Final Checklist","index":3,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[[1,1,1,2]]}
[]
[null,"There are a number of things that we will check your file for - so please ensure that you've tripled checked that your solution addresses each of these variables!"]
[null,1,"Is each student assigned to a stop?"]
[null,2,"Is each student walking less to their stop than their max walk-to-stop distance assigned in the dataset?"]
[null,3,"Is each student on a bus that goes to their school - and one that will arrive at their school at the appropriate time?"]
[null,4,"Is each student on the bus for less than an hour? (There is an exception here: if a student is the only student going to a given school - and that school is more than 1 hour away - the student can be on the bus for more than an hour)"]
[null,5,"Are any buses oversubscribed? That is, are there more passengers on a given bus than that bus can fit?"]
[null,6,"Does each bus begin and end the morning and afternoon at their assigned yard?"]
[null,7,"Does every student's assigned bus go to their assigned bus stop?"]